package com.jdbc.homework;

import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
/**
 * ClassName: JDBCDemo
 * Passage: com.jdbc
 * Description:
 *
 * @Author 余大帅逼
 * @Create 2025/2/24 17:12
 * @Version 1.0
 **/
public class demo01 {

    public static final String URL = "jdbc:mysql://localhost:3306/mysql?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8";
    public static final String USER = "root";
    public static final String PASSWORD = "LIyu19650726";

    public static boolean login(String username, String password) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "SELECT * FROM t_user WHERE username=? AND password=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void showAllStudents() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "SELECT * FROM student";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id") + ", Name: " + resultSet.getString("name") + ", Age: " + resultSet.getInt("age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void addStudent(String name, int age) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "INSERT INTO student (name, age) VALUES (?, ?)";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.executeUpdate();
            System.out.println("学生添加成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void deleteStudent(int id) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "DELETE FROM student WHERE id=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
            System.out.println("学生删除成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void updateStudent(int id, String name, int age) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "UPDATE student SET name=?, age=? WHERE id=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setInt(3, id);
            preparedStatement.executeUpdate();
            System.out.println("学生更新成功！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in, StandardCharsets.UTF_8.name());

        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();

        if (login(username, password)) {
            System.out.println("登录成功！");

            while (true) {
                System.out.println("\n选择菜单：");
                System.out.println("1) 显示所有学生");
                System.out.println("2) 添加学生");
                System.out.println("3) 删除学生");
                System.out.println("4) 更新学生");
                System.out.println("5) 退出");
                System.out.print("请输入选择：");

                int choice = scanner.nextInt();
                scanner.nextLine();  // Consume newline

                switch (choice) {
                    case 1:
                        showAllStudents();
                        break;
                    case 2:
                        System.out.print("请输入学生姓名：");
                        String name = scanner.nextLine();
                        System.out.print("请输入学生年龄：");
                        int age = scanner.nextInt();
                        scanner.nextLine();  // Consume newline
                        addStudent(name, age);
                        break;
                    case 3:
                        System.out.print("请输入学生学号：");
                        int id = scanner.nextInt();
                        deleteStudent(id);
                        break;
                    case 4:
                        System.out.print("请输入学生学号：");
                        id = scanner.nextInt();
                        scanner.nextLine();  // Consume newline
                        System.out.print("请输入学生姓名：");
                        name = scanner.nextLine();
                        System.out.print("请输入学生年龄：");
                        age = scanner.nextInt();
                        updateStudent(id, name, age);
                        break;
                    case 5:
                        System.out.println("退出系统！");
                        return;
                    default:
                        System.out.println("无效选择，请重新输入！");
                }
            }
        } else {
            System.out.println("登录失败，请检查用户名和密码！");
        }
    }
}